﻿using SPDbFileExtractor.Framework.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace SPDbFileExtractor.Framework.Code
{
    public class ContentDbManager
    {
        /// <summary>
        /// Verifies the db version.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static bool VerifyDbVersion(string connectionString)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var versionSelectCommand = SqlBuilder.GetVersionSelectCommand();
            var tableValidationCommand = SqlBuilder.GetTableValidationCommand();

            var data = new DataTable();

            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    using (SqlCommand cmd = new SqlCommand(tableValidationCommand, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(versionSelectCommand, conn))
                    {
                        da.Fill(data);
                    }
                }

                var version = data.Rows[0]["Version"] as string;

                if (version == null || version.Trim().Length == 0 || !version.StartsWith("14.0"))
                {
                    ConnectionManager.ConnectionValid = false;

                    return false;
                }

                ConnectionManager.ConnectionValid = true;

                return true;
            }
            catch
            {
                ConnectionManager.ConnectionValid = false;

                return false;
            }
        }

        /// <summary>
        /// Gets all sites.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static List<Site> GetSites(string connectionString)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var cmd = SqlBuilder.BuildSelectSitesCommand();
            var data = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using(SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
                {
                    da.Fill(data);
                }
            }

            return Site.GetCollectionFromTable(data);
        }

        /// <summary>
        /// Gets the webs.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="siteId">The site id.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static List<Web> GetWebs(string connectionString, Guid siteId)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var cmd = SqlBuilder.BuildSelectWebsCommand(siteId);
            var data = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
                {
                    da.Fill(data);
                }
            }

            return Web.GetCollectionFromTable(data);
        }

        /// <summary>
        /// Gets the lists.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="webId">The web id.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static List<List> GetLists(string connectionString, Guid webId)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var cmd = SqlBuilder.BuildSelectListsCommand(webId);
            var data = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
                {
                    da.Fill(data);
                }
            }

            return List.GetCollectionFromTable(data);
        }

        /// <summary>
        /// Gets the items.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="listId">The list id.</param>
        /// <param name="fileNameFilter"> </param>
        /// <param name="dirFilter"> </param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static List<ListItem> GetItems(string connectionString, Guid listId, string fileNameFilter, string dirFilter)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var cmd = SqlBuilder.BuildSelectListItemsCommand(listId, fileNameFilter, dirFilter);
            var data = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
                {
                    da.Fill(data);
                }
            }

            return ListItem.GetCollectionFromTable(data);
        }

        /// <summary>
        /// Gets the item versions.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="itemId">The item id.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static List<FileVersion> GetItemVersions(string connectionString, Guid itemId)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var cmd = SqlBuilder.BuildSelectItemVersionsCommand(itemId);
            var data = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
                {
                    da.Fill(data);
                }
            }

            return FileVersion.GetCollectionFromTable(data);
        }

        /// <summary>
        /// Gets the content of the item.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="itemId">The item id.</param>
        /// <param name="version"> </param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">connectionString</exception>
        public static FileContent GetItemContent(string connectionString, Guid itemId, int version)
        {
            if (connectionString == null || connectionString.Trim().Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            var cmd = SqlBuilder.BuildSelectItemContentCommand(itemId, version);
            var data = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
                {
                    da.Fill(data);
                }
            }

            return FileContent.GetSingleItemFromTable(data);
        }
    }
}
